package com.digitalgeko.hourreporter.database;

import android.content.*;
import android.database.sqlite.*;

public class DataBaseManager extends SQLiteOpenHelper {

	/**
	 * General DataBase Information
	 */
	public static final String CREATE_TABLE = "CREATE TABLE ";
	public static final String PRIMARY_KEY = " INTEGER PRIMARY KEY AUTOINCREMENT, ";
	public static final String FOREIGN_KEY = "FOREIGN KEY"; 
	public static final String REFERENCES = "REFERENCES ";
	public static final String UNIQUE = " UNIQUE";
	public static final String NOT_NULL = " NOT NULL";
	public static final String TYPE_TEXT = " TEXT";
	public static final String TYPE_INTEGER = " INTEGER";
	public static final String TYPE_NUMERIC = " NUMERIC";
	public static final String TYPE_FECHA = " CURRENT_DATE";
	public static final String TYPE_HORA = " CURRENT_TIME";
	public static final String TYPE_DATE = " CURRENT_TIMESTAMP";
	
	/**
	 * General Information of the DataBase
	 */
	public static final String DATABASE_NAME = "HourReporter.db";
	public static final int DATABASE_VERSION = 1;
	
	/**
	 * Security User Information Table
	 */
	public static final String USER_TABLE = "SecurityUserTable";
	public static final String USER_FIELD_ID = "UserId";
	public static final String USER_FIELD_USER = "UserName";
	public static final String USER_FIELD_PASSWORD = "UserPass";
	
	/**
	 * Proyect and Task Table
	 */
	public static final String PROY_TASK_TABLE = "ProyectTaskTable";
	public static final String PROY_TASK_FIELD_ID = "ProyectTaskId";
	public static final String PROY_TASK_FIELD_NAME = "ProyectTaskName";
	public static final String PROY_TASK_FIELD_DESCRIPTION = "ProyectTaskDescription";
	public static final String PROY_TASK_FIELD_STATUS = "ProyectTaskStatus";
	public static final String PROY_TASK_FIELD_FATHER = "ProyectTaskFather";
	public static final String PROY_TASK_FIELD_USER = "Usuario";
	
	/**
	 * Report Table
	 */
	public static final String REPORT_TABLE = "ReportTable";
	public static final String REPORT_FIELD_ID = "ReportId";
	public static final String REPORT_FIELD_PROYECT = "Proyect";
	public static final String REPORT_FIELD_TASK = "Task";
	public static final String REPORT_FIELD_COMMENT = "Comment";
	public static final String REPORT_FIELD_DATE = "Fecha";
	public static final String REPORT_FIELD_TIME = "Tiempo";
	public static final String REPORT_FIELD_USER = "Usuario";
	
	public DataBaseManager(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_TABLE + USER_TABLE + 
				   " (" + USER_FIELD_ID + PRIMARY_KEY +
				   USER_FIELD_USER + TYPE_TEXT + UNIQUE + NOT_NULL + ", " +
				   USER_FIELD_PASSWORD + TYPE_TEXT + NOT_NULL + ");");
		
		db.execSQL(CREATE_TABLE + PROY_TASK_TABLE +
				   " (" + PROY_TASK_FIELD_ID + PRIMARY_KEY +
				   PROY_TASK_FIELD_NAME + TYPE_TEXT + NOT_NULL + ", " +
				   PROY_TASK_FIELD_DESCRIPTION + TYPE_TEXT + ", " +
				   PROY_TASK_FIELD_STATUS + TYPE_INTEGER + ", " +
				   PROY_TASK_FIELD_FATHER + TYPE_INTEGER + ", " +
				   PROY_TASK_FIELD_USER + TYPE_INTEGER + NOT_NULL + ", " + 
				   FOREIGN_KEY + " (" + PROY_TASK_FIELD_USER + ") " +
				   REFERENCES + USER_TABLE + "(" + USER_FIELD_ID + "), " +
				   FOREIGN_KEY + " (" + PROY_TASK_FIELD_FATHER + ") " + 
				   REFERENCES + PROY_TASK_TABLE + "(" + PROY_TASK_FIELD_ID + "));");
		
		db.execSQL(CREATE_TABLE + REPORT_TABLE +
				   " (" + REPORT_FIELD_ID + PRIMARY_KEY + 
				   REPORT_FIELD_PROYECT + TYPE_INTEGER + NOT_NULL + ", " +
				   REPORT_FIELD_TASK + TYPE_INTEGER + ", " +
				   REPORT_FIELD_COMMENT + TYPE_TEXT + ", " +
				   REPORT_FIELD_DATE + TYPE_FECHA + ", " + 
				   REPORT_FIELD_TIME + TYPE_HORA + ", " +
				   REPORT_FIELD_USER + TYPE_INTEGER + NOT_NULL + ", " +
				   FOREIGN_KEY + " (" + REPORT_FIELD_USER + ") " +
				   REFERENCES + USER_TABLE + "(" + USER_FIELD_ID + "), " +
				   FOREIGN_KEY + " (" + REPORT_FIELD_PROYECT + ") " + 
				   REFERENCES + PROY_TASK_TABLE + "(" + PROY_TASK_FIELD_ID + "), " +
				   FOREIGN_KEY + " (" + REPORT_FIELD_TASK + ") " +
				   REFERENCES + PROY_TASK_TABLE + "(" + PROY_TASK_FIELD_ID + "));");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
	}

}
